package view;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class StateComplaintDAO {
	private DBConnect con = null;
	 
    public StateComplaintDAO (DBConnect incon)
    {
        con = incon;
        try {con.getConnection().setAutoCommit(false);}
        catch (SQLException e){}
    }
    public String ViewStateComplaint(String instate)
    {
        PreparedStatement ps = null;
        ResultSet rs = null;
        String State = "";
        String Month = "";
        String Row = "";
        StringBuffer Query = new StringBuffer(2000);
        int Complaint = 0;
        
        try
        {
          ps=con.getConnection().prepareStatement(""
          		+ "WITH STATE_SELECTION AS "
        		+"(SELECT PERSON_ID, FIRST_NAME, LAST_NAME, STATE "
        		+"FROM "+con.getSchema()+".PERSON "
        		+"WHERE STATE = ? ), "
        		+"COMPLAINT_OF_STATE AS "
        		+"(SELECT PERSON_ID, FIRST_NAME, LAST_NAME, STATE, " 
        		+"TO_CHAR(CC.COMPLAINT_DATE, 'MONTH YYYY') AS MONTHS "
        		+"FROM STATE_SELECTION SS "
        		+"INNER JOIN "+con.getSchema()+".FACTORY_ORDER FO "
        		+"ON SS.PERSON_ID = FO.CUSTOMER_ID "
        		+"INNER JOIN "+con.getSchema()+".CUST_COMPLAINT CC "
        		+"ON FO.ORDER_ID = CC.ORDER_ID), "
        		+"LIST_OF_MONTHS AS "
        		+"(SELECT STATE, MONTHS, COUNT(MONTHS) AS COMPLAINTS "
        		+"FROM COMPLAINT_OF_STATE "
        		+"GROUP BY STATE, MONTHS "
        		+"ORDER BY COMPLAINTS DESC) "
        		+"SELECT STATE, MONTHS, COMPLAINTS "
        		+"FROM LIST_OF_MONTHS "
        		+"WHERE ROWNUM <=5 ");
          ps.setString(1,instate);
          final long startTime = System.nanoTime();
          rs=ps.executeQuery();
          Query.append(String.format("%1$-25s %2$-30s \t %3$-15s \n", "State", "Month", "Complaints"));
            while(rs.next())
            {
            	State = rs.getString(1);
            	Month = rs.getString(2);
            	Complaint = rs.getInt(3);
            	
            	Row = String.format("%1$-20s %2$-30s \t %3$-15s \n", State,  Month, Complaint);
            	Query.append(Row);
            }
          final long duration = System.nanoTime() - startTime;
          Query.append("\n Display Complete. Elapsed: " + (duration/ 1000000000.0) + " seconds \n");
          rs.close();
          ps.close();
        }
        catch(SQLException e)
        {
            System.out.println("SQL Error in State Complaint Report" + e.getSQLState());
            System.out.println("\nError Code: " + e.getErrorCode());
            System.out.println("\nMessage: " + e.getMessage());
            System.exit( 1 );
        }
        catch(Exception e)
        {
            System.out.println("unknown Error in State Complaint Report");
            System.out.println("\nMessage: " + e.getMessage());
            System.exit( 1 );
        }
       return Query.toString();
    }
}
